# -*- coding: utf-8 -*-

import pymysql


class HousePipeline(object):
    home_insert = '''insert into home (title, details, total_price, unit_price )
							values('{title}', '{details}', '{total_price}', '{unit_price}' )'''

    def __init__(self, settings):
        self.settings = settings

    def process_item(self, item, spider):
        """
        item process
        """
        title = item['title']
        if title:
            item['title'] = title.strip()

        print(item)

        sqltext = self.home_insert.format(
            title=pymysql.escape_string(item['title']),
            details=pymysql.escape_string(item['details']),
            total_price=pymysql.escape_string(item['total_price']),
            unit_price=pymysql.escape_string(item['unit_price'])
        )
        self.cursor.execute(sqltext)

        return item

    @classmethod
    def from_crawler(cls, crawler):
        return cls(crawler.settings)

    def open_spider(self, spider):
        # connet database
        self.connect = pymysql.connect(
            host=self.settings.get('MYSQL_HOST'),
            port=self.settings.get('MYSQL_PORT'),
            db=self.settings.get('MYSQL_DBNAME'),
            user=self.settings.get('MYSQL_USER'),
            passwd=self.settings.get('MYSQL_PASSWD'),
            charset='utf8',
            use_unicode=True)

        # insert into database through cursor
        self.cursor = self.connect.cursor();
        self.connect.autocommit(True)

    def close_spider(self, spider):
        self.cursor.close()
        self.connect.close()
